DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student  (
    sn       INTEGER,           -- 自增序号（从10000开始）
    no       VARCHAR(10)        -- 学号（格式：230650118）
        CHECK (no ~ '^\d{9}$'), -- 8位数字校验
    name     TEXT NOT NULL,
    gender   CHAR(1)            -- 性别F/M
        CHECK (gender IN ('F', 'M')),
    enrollment_date DATE        -- 入学日期
        CHECK (enrollment_date > '2000-01-01'), -- 合理范围校验
    PRIMARY KEY (sn),
    UNIQUE (no)                 -- 学号唯一
);

-- 给sn创建一个自增序号
CREATE SEQUENCE seq_student_sn 
    START 10000 INCREMENT 1 OWNED BY student.sn;
ALTER TABLE student ALTER sn 
    SET DEFAULT nextval('seq_student_sn');
-- 学号唯一
CREATE UNIQUE INDEX idx_student_no ON student(no);

-- === 课程表
DROP TABLE IF EXISTS course;
CREATE TABLE IF NOT EXISTS course  (
    sn       INTEGER,          -- 自增序号（从20000开始）
    no       VARCHAR(5)        -- 课程号（格式：10055）
        CHECK (no ~ '^\d{5}$'),
    name     TEXT NOT NULL,    -- 课程名
    credit   NUMERIC(5,2)      -- 学分
        CHECK (credit > 0),
    hours    INTEGER           -- 学时
        CHECK (hours > 0),
    PRIMARY KEY (sn),
    UNIQUE (no)                -- 课程号唯一
);

CREATE SEQUENCE seq_course_sn 
    START 20000 INCREMENT 1 OWNED BY course.sn;
ALTER TABLE course ALTER sn 
    SET DEFAULT nextval('seq_course_sn');
-- CREATE UNIQUE INDEX idx_course_no ON course(no);



-- === 班次表
DROP TABLE IF EXISTS class;
CREATE TABLE IF NOT EXISTS class  (
    sn       INTEGER,          -- 自增序号（从30000开始）
    class_no VARCHAR(15)       -- 班次号（格式：10055-2023S1-01）
        CHECK (class_no ~ '^\d{5}-\d{4}S[12]-\d{2}$'), -- 新校验规则
    name     TEXT,             -- 可选的班次描述
    semester VARCHAR(11)       -- 学期（格式：2024-2025-1）
        CHECK (semester ~ '^\d{4}-\d{4}-[12]$'),
    location TEXT,
    cou_sn   INTEGER,          -- 关联课程sn
    PRIMARY KEY (sn),
    FOREIGN KEY (cou_sn) REFERENCES course(sn),
    UNIQUE (class_no)          -- 班次号唯一
);

CREATE SEQUENCE seq_class_sn 
    START 30000 INCREMENT 1 OWNED BY class.sn;
ALTER TABLE class ALTER COLUMN sn 
    SET DEFAULT nextval('seq_class_sn');

-- 修改班次号校验规则
ALTER TABLE class 
    DROP CONSTRAINT class_class_no_check,
    ADD CONSTRAINT class_class_no_check 
        CHECK (class_no ~ '^\d{5}-\d{4}S[12]-\d{2}$');

-- 添加班次序号字段（可选，或通过班次号解析）
ALTER TABLE class ADD COLUMN class_seq SMALLINT;
-- 在class表中添加更新时间戳字段
ALTER TABLE class ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();

-- === 选课关联表（学生与班次关系）
DROP TABLE IF EXISTS class_student;
CREATE TABLE IF NOT EXISTS class_student (
    id          SERIAL PRIMARY KEY,         -- 自增主键
    stu_sn      INTEGER NOT NULL            -- 学生序号（关联student.sn），确保学生删除时自动清理关联记录
        REFERENCES student(sn) ON DELETE CASCADE,
    class_sn    INTEGER NOT NULL            -- 班次序号（关联class.sn）
        REFERENCES class(sn) ON DELETE CASCADE,
    created_at  TIMESTAMP DEFAULT NOW(),    -- 选课时间（自动记录创建时间）
    -- 唯一约束：防止重复关联
    UNIQUE (stu_sn, class_sn)
);

-- 新增级联更新
ALTER TABLE class_student 
  DROP CONSTRAINT class_student_class_sn_fkey,
  ADD CONSTRAINT class_student_class_sn_fkey 
    FOREIGN KEY (class_sn) REFERENCES class(sn)
    ON UPDATE CASCADE;  
ALTER TABLE class_student 
  ADD CONSTRAINT uniq_class_student 
    UNIQUE (class_sn, stu_sn);

-- === 成绩表
DROP TABLE IF EXISTS class_grade;
CREATE TABLE IF NOT EXISTS class_grade  (
    id       SERIAL PRIMARY KEY,  -- 自增主键（便于单独操作记录）
    stu_sn   INTEGER NOT NULL,    -- 学生序号（关联student.sn）
    class_sn INTEGER NOT NULL,    -- 班次序号（关联class.sn）
    grade    NUMERIC(5,2)         -- 成绩（允许NULL表示未录入）
        CHECK (grade BETWEEN 0 AND 100 OR grade IS NULL),
    -- 唯一约束：一个学生在同一班次只能有一条成绩记录
    UNIQUE (stu_sn, class_sn),
    updated_at TIMESTAMP DEFAULT NOW(),  -- 新增更新时间戳
    -- 外键约束
    CONSTRAINT fk_student FOREIGN KEY (stu_sn) 
        REFERENCES student(sn) ON DELETE CASCADE,
    CONSTRAINT fk_class FOREIGN KEY (class_sn) 
        REFERENCES class(sn) ON DELETE CASCADE
);

-- === 教秘用户表（存储基础信息）
DROP TABLE IF EXISTS sys_users;
CREATE TABLE IF NOT EXISTS sys_users (
    user_sn SERIAL PRIMARY KEY,          -- 自增序号
    username VARCHAR(50) UNIQUE NOT NULL, -- 用户名（唯一）
    real_name VARCHAR(50) NOT NULL,       -- 真实姓名（用于显示）
    created_at TIMESTAMP DEFAULT NOW()    -- 创建时间
);

CREATE SEQUENCE seq_users_sn 
    START 90000 INCREMENT 1 OWNED BY sys_users.user_sn;
ALTER TABLE sys_users ALTER COLUMN user_sn
    SET DEFAULT nextval('seq_users_sn');

-- === 密码表（存储哈希值，与用户表关联）
DROP TABLE IF EXISTS user_passwords;
CREATE TABLE IF NOT EXISTS user_passwords (
    id SERIAL PRIMARY KEY,
    user_sn INTEGER UNIQUE NOT NULL REFERENCES sys_users(user_sn), -- 关联用户
    hashed_password VARCHAR(100) NOT NULL                          -- 密码哈希值
);


-- 为常用查询添加索引
CREATE INDEX idx_class_semester ON class(semester);
CREATE INDEX idx_class_student_stu ON class_student(stu_sn);
CREATE INDEX idx_class_student_class ON class_student(class_sn);
CREATE INDEX idx_class_student_composite ON class_student (class_sn, stu_sn);
CREATE INDEX idx_class_grade_student ON class_grade(stu_sn);
CREATE INDEX idx_class_grade_class ON class_grade(class_sn);
CREATE INDEX idx_user_passwords_user_sn ON user_passwords(user_sn);
CREATE INDEX idx_sys_users_name ON sys_users USING BTREE (username);


-- 优化多条件查询
CREATE INDEX IF NOT EXISTS idx_grade_stu_class ON class_grade(stu_sn, class_sn);

-- 加速课程关联查询
CREATE INDEX IF NOT EXISTS idx_class_course ON class 
    USING BTREE (sn, cou_sn);

-- === 确保一个学生只能关联到同一课程的一个班次
-- 添加辅助列并创建函数索引
ALTER TABLE class_student ADD COLUMN cou_sn INTEGER;

-- 通过触发器或更新语句维护class_student 表的 cou_sn 列
CREATE OR REPLACE FUNCTION set_class_cou_sn()
RETURNS TRIGGER AS $$
BEGIN
    NEW.cou_sn := (SELECT cou_sn FROM class WHERE sn = NEW.class_sn);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_cou_sn
BEFORE INSERT OR UPDATE ON class_student
FOR EACH ROW EXECUTE FUNCTION set_class_cou_sn();

-- 创建唯一索引：(stu_sn, cou_sn) 的唯一性约束
CREATE UNIQUE INDEX idx_student_course_unique 
ON class_student (stu_sn, cou_sn);


-- === 导入日志表
CREATE TABLE IF NOT EXISTS grade_import_logs (
    id SERIAL PRIMARY KEY,
    class_sn INTEGER NOT NULL REFERENCES class(sn),
    stu_sn INTEGER NOT NULL REFERENCES student(sn),
    operator INTEGER NOT NULL REFERENCES sys_users(user_sn),
    grade NUMERIC(5,2),
    created_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT valid_grade CHECK (grade BETWEEN 0 AND 100 OR grade IS NULL)
);

-- 添加日志索引
CREATE INDEX idx_import_log_class ON grade_import_logs(class_sn);
CREATE INDEX idx_import_log_operator ON grade_import_logs(operator);


-- === 审计日志表
CREATE TABLE IF NOT EXISTS grade_audit_log (
    id SERIAL PRIMARY KEY,
    class_sn INTEGER NOT NULL REFERENCES class(sn),
    stu_sn INTEGER NOT NULL REFERENCES student(sn),
    old_grade NUMERIC(5,2),
    new_grade NUMERIC(5,2),
    operator INTEGER NOT NULL REFERENCES sys_users(user_sn),
    changed_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_audit_class ON grade_audit_log(class_sn);
CREATE INDEX idx_audit_student ON grade_audit_log(stu_sn);


-- === 报表视图
CREATE VIEW student_grade_report AS
SELECT 
    s.sn AS stu_sn,
    s.no AS stu_no,
    c.sn AS course_sn,
    cl.sn AS class_sn,
    g.grade,
    g.updated_at AS grade_updated
FROM student s
JOIN class_student cs ON s.sn = cs.stu_sn  -- 先连接学生与班次关系
JOIN class cl ON cs.class_sn = cl.sn       -- 再连接班次信息
JOIN course c ON cl.cou_sn = c.sn          -- 最后连接课程信息
LEFT JOIN class_grade g ON g.stu_sn = s.sn AND g.class_sn = cl.sn;  -- 左联成绩表
